//
//  TEDatabaseViewController.m
//  TintExample
//
//  Created by Kevin Wu on 2/28/12.
//  Copyright (c) 2012 Telligenty. All rights reserved.
//

#import "TEDatabaseViewController.h"
#import "TEDBUser.h"
#import "TEDBBuddy.h"

@implementation TEDatabaseViewController



#pragma mark - Memory

- (id)init {
    self = [super init];
    if (self) {
    }
    return self;
}

- (void)dealloc {
    [super dealloc];
}



#pragma mark - UIViewController

- (void)loadView {
    [super loadView];
}

- (void)viewDidLoad {
    [super viewDidLoad];
    
    [self addButtonWithTitle:@"Create database" selector:@selector(createDatabase:) type:1];
    [self addButtonWithTitle:@"Create table" selector:@selector(createTable:) type:1];
    [self addButtonWithTitle:@"Insert rows" selector:@selector(insertRows:) type:1];
    [self addButtonWithTitle:@"Insert two same row" selector:@selector(insertTwoSameRow:) type:1];
    
    [self addButtonWithTitle:@"Query: between int" selector:@selector(query1:) type:2];
    [self addButtonWithTitle:@"Query: string" selector:@selector(query2:) type:2];
    [self addButtonWithTitle:@"Query: date" selector:@selector(query3:) type:2];
    
    [self addButtonWithTitle:@"Test bad sql" selector:@selector(testBadSQL:) type:3];
    [self addButtonWithTitle:@"Show error" selector:@selector(showError:) type:3];
    [self addButtonWithTitle:@"Is thread safe" selector:@selector(isThreadSafe:) type:3];
    
    [self addButtonWithTitle:@"Good connection" selector:@selector(goodConnection:) type:4];
    [self addButtonWithTitle:@"Is table exist" selector:@selector(isTableExist:) type:4];
}

- (void)createDatabase:(id)sender {
    NSString *path = [TTDocumentsPath() stringByAppendingPathComponent:@"db1.sqlite"];
    TTDatabase *db = [TTDatabase sharedObject];
    db.databasePath = path;
    [db open];
}

- (void)createTable:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    [db executeUpdateString:@"CREATE TABLE table1(a INTEGER PRIMARY KEY, b REAL, c TEXT);"];
    [db executeUpdateString:@"CREATE TABLE table2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER, d REAL, e REAL, f TEXT);"];
    
}

- (void)insertRows:(id)sender {
    TTDatabase *db = [TTDatabase sharedObject];
    
    int i = 0;
    while (i++ < 20) {
        sqlite3_int64 rowid = [db executeInsert:@"INSERT INTO table1(a, b, c) VALUES(?, ?, ?);", 
                               [NSNumber numberWithInt:i], 
                               [NSNumber numberWithDouble:1.01234567890123456789+arc4random()%100], 
                               [NSString stringWithFormat:@"%c", 'A'+i-1], nil];
        NSLog(@"%lld", rowid);
    }
    
    sqlite3_int64 rowid = 0;
    
    
    NSString *sql = @"INSERT INTO table2(a, b, c, d, e, f) VALUES(?, ?, ?, ?, ?, ?);";
    rowid = [db executeInsert:sql, 
             [NSNumber numberWithInt:1], 
             [NSNumber numberWithBool:YES], 
             [NSNumber numberWithLongLong:1152921504606000000L], 
             [NSNumber numberWithDouble:10.01234567890123], 
             [[NSDate date] dateBySubtractingDays:3], 
             @"angel", nil];
    NSLog(@"%lld", rowid);
    
    rowid = [db executeUpdate:sql, 
             [NSNumber numberWithInt:2], 
             [NSNumber numberWithBool:NO], 
             [NSNumber numberWithLongLong:125899906842000L], 
             [NSNumber numberWithDouble:11.01234567890123456789], 
             [[NSDate date] dateByAddingDays:3], 
             @"god", nil];
    NSLog(@"%lld", rowid);
}

- (void)insertTwoSameRow:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    [db executeUpdateString:@"INSERT INTO table1(a, b, c) VALUES(100, 2.1, '3');"];
    [db executeUpdateString:@"INSERT INTO table1(a, b, c) VALUES(100, 2.1, '3');"];
}




- (void)query1:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    TTDatabaseResult *result = [db executeQuery:@"SELECT * FROM table1 WHERE a>? AND a<?;", 
                                [NSNumber numberWithInt:3], 
                                [NSNumber numberWithInt:10], nil];
    
    for (int i=0; i<[result count]; ++i) {
        TTDatabaseRow *row = [result rowAtIndex:i];
        
        NSLog(@"%d %lf %@", 
              [row intForColumn:@"a"], 
              [row doubleForColumn:@"b"], 
              [row stringForColumn:@"c"]);
    }
    
    
    result = [db executeQueryString:@"SELECT * FROM table2;"];
    for (int i=0; i<[result count]; ++i) {
        TTDatabaseRow *row = [result rowAtIndex:i];
        
        NSLog(@"%d %d %lld %lf %@ %@", 
              [row intForColumn:@"a"], 
              [row boolForColumn:@"b"], 
              [row longLongForColumn:@"c"], 
              [row doubleForColumn:@"d"], 
              [row dateForColumn:@"e"], 
              [row stringForColumn:@"f"]);
    }
}

- (void)query2:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    TTDatabaseResult *result = [db executeQuery:@"SELECT * FROM table1 WHERE c=?;", @"B", nil];
    for (int i=0; i<[result count]; ++i) {
        TTDatabaseRow *row = [result rowAtIndex:i];
        
        NSLog(@"%d %lf %@", 
              [row intForColumn:@"a"], 
              [row doubleForColumn:@"b"], 
              [row stringForColumn:@"c"]);
    }
}

- (void)query3:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    TTDatabaseResult *result = [db executeQuery:@"SELECT * FROM table2 WHERE e>?;", [NSDate date], nil];
    for (int i=0; i<[result count]; ++i) {
        TTDatabaseRow *row = [result rowAtIndex:i];
        
        NSLog(@"%d %d %lld %lf %@ %@", 
              [row intForColumn:@"a"], 
              [row boolForColumn:@"b"], 
              [row longLongForColumn:@"c"], 
              [row doubleForColumn:@"d"], 
              [row dateForColumn:@"e"], 
              [row stringForColumn:@"f"]);
    }
}





- (void)testBadSQL:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    [db executeUpdate:@"blah blah blah", nil];
    if ([db hadError]) {
        TTDPRINT(@"Error %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}

- (void)showError:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    if ([db hadError]) {
        TTDPRINT(@"Error %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}

- (void)isThreadSafe:(id)sender {
    if ([TTDatabase isSQLiteThreadSafe]) {
        TTDPRINT(@"Sqlite is thread safe");
    } else {
        TTDPRINT(@"Sqlite is not thread safe");
    }
}


- (void)goodConnection:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    if ([db goodConnection]) {
        NSLog(@"Good connection");
    } else {
        NSLog(@"No connection");
    }
    
}


- (void)isTableExist:(id)sender {
    
    TTDatabase *db = [TTDatabase sharedObject];
    
    if ([db hasTable:@"table1"]) {
        NSLog(@"Has table1");
    } else {
        NSLog(@"Don't have table1");
    }
    
    if ([db hasTable:@"girl"]) {
        NSLog(@"Has girl");
    } else {
        NSLog(@"Don't have girl");
    }
    
}

@end
